﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using MQTT.Server.Host.Entity;
using MQTT.Server.Host.WebAPI.Models;

namespace MQTT.Server.Host.Controller
{
    public static class DataController
    {
        /// <summary>
        /// 添加设备
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static int AddDevice(string name)
        {
            string sql = $"INSERT INTO MqttDevice(Name, IsOnline) " +
                         $"VALUES('{name}', 0); SELECT LAST_INSERT_ROWID()";
            int index = Convert.ToInt32(App.OperateSql.ExecuteScalar(sql).ToString());
            return index;
        }

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="value"></param>
        /// <param name="dataTagId"></param>
        /// <returns></returns>
        public static int AddData(double value, int dataTagId)
        {
            string sql = $"INSERT INTO DataHistory(Value, DeviceDataTagId, Time) " +
                         $"VALUES({value}, {dataTagId}, '{DateTime.Now:yyyy-MM-dd HH:mm:ss}')";
            int index = App.OperateSql.ExecuteNoQuery(sql);
            return index;
        }

        /// <summary>
        /// 删除设备
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static int RemoveDevice(int id)
        {
            // 删除设备
            string sql = $"DELETE FROM MqttDevice WHERE Id = {id}";
            int index = App.OperateSql.ExecuteNoQuery(sql);

            // 删除关联点
            string sql2 = $"DELETE FROM DeviceDataTag WHERE DeviceId = {id}";
            App.OperateSql.ExecuteNoQuery(sql2);

            return index;
        }

        /// <summary>
        /// 设置设备在线状态
        /// </summary>
        /// <param name="deviceId"></param>
        /// <param name="isOnline"></param>
        /// <returns></returns>
        public static int SetDeviceIsOnline(int deviceId, bool isOnline)
        {
            int online = isOnline ? 1 : 0;
            string sql = $"UPDATE MqttDevice SET IsOnline = {online} WHERE Id = {deviceId}";
            int index = App.OperateSql.ExecuteNoQuery(sql);
            return index;
        }

        /// <summary>
        /// 添加数据点
        /// </summary>
        /// <param name="deviceId">设备id</param>
        /// <param name="tag">标识</param>
        /// <param name="dataTypeId">数据类型id</param>
        /// <returns></returns>
        public static int AddDataTag(int deviceId, string tag, int dataTypeId)
        {
            string sql = $"INSERT INTO DeviceDataTag(Tag, DeviceId, DataTypeId) " +
                         $"VALUES('{tag}', {deviceId}, {dataTypeId})";
            int index = App.OperateSql.ExecuteNoQuery(sql);
            return index;
        }

        /// <summary>
        /// 获取设备
        /// </summary>
        /// <param name="id">设备id</param>
        /// <returns></returns>
        public static DeviceEntity GetDevice(int id)
        {
            DeviceEntity device = null;

            // 获取设备信息
            string sql = $"SELECT * " +
                         $"FROM MqttDevice " +
                         $"WHERE Id = {id}";
            var table = App.OperateSql.GetTable(sql);
            if (table.Rows.Count < 1)
            {
                return null;
            }

            DataRow row = table.Rows[0];
            device = new DeviceEntity();
            device.Id = Convert.ToInt32(row["Id"].ToString());
            device.Name = row["Name"].ToString();
            device.IsOnline = Convert.ToByte(row["isOnline"].ToString());
            return device;
        }

        /// <summary>
        /// 获取所有的设备数据类型
        /// </summary>
        /// <returns></returns>
        public static List<DeviceDataTypeEntity> GetDataTypes()
        {
            List<DeviceDataTypeEntity> result = new List<DeviceDataTypeEntity>();
            string sql = $"SELECT * " +
                         $"FROM DataType ";
            var table = App.OperateSql.GetTable(sql);
            foreach (DataRow row in table.Rows)
            {
                var device = new DeviceDataTypeEntity();
                device.Id = Convert.ToInt32(row["Id"].ToString());
                device.Name = row["Name"].ToString();
                device.Unit = row["Unit"].ToString();
                device.Formula = row["Formula"].ToString();
                result.Add(device);
            }

            return result;
        }

        /// <summary>
        /// 获取设备关联的数据点
        /// </summary>
        /// <param name="deviceId"></param>
        /// <returns></returns>
        public static List<DeviceDataTagEntity> GetDeviceDataTags(int deviceId)
        {
            List<DeviceDataTagEntity> result = new List<DeviceDataTagEntity>();
            string sql = $"SELECT Id, Tag, DataTypeId " +
                         $"FROM DeviceDataTag " +
                         $"WHERE DeviceId = {deviceId}";

            var table = App.OperateSql.GetTable(sql);
            foreach (DataRow row in table.Rows)
            {
                DeviceDataTagEntity entity = new DeviceDataTagEntity();
                entity.Id = Convert.ToInt32(row["Id"].ToString());
                entity.DataTypeId = Convert.ToInt32(row["DataTypeId"].ToString());
                entity.Tag = row["Tag"].ToString();
                result.Add(entity);
            }

            return result;
        }

        /// <summary>
        /// 获取数据类型id
        /// </summary>
        /// <param name="deviceId"></param>
        /// <param name="tag"></param>
        /// <returns></returns>
        public static int[] GetDataTagIdAndDataTypeId(int deviceId, string tag)
        {
            int[] values = new int[2];
            string sql = $"SELECT Id, DataTypeId " +
                         $"FROM DeviceDataTag " +
                         $"WHERE DeviceId = {deviceId} AND Tag = '{tag}'";

            var table = App.OperateSql.GetTable(sql);
            if (table.Rows.Count > 0)
            {
                DataRow row = table.Rows[0];
                values[0] = Convert.ToInt32(row["Id"].ToString());
                values[1] = Convert.ToInt32(row["DataTypeId"].ToString());
            }


            return values;
        }


        /// <summary>
        /// 获取所有设备
        /// </summary>
        /// <returns></returns>
        public static List<DeviceEntity> GetDevices()
        {
            List<DeviceEntity> result = new List<DeviceEntity>();
            string sql = $"SELECT * " +
                         $"FROM MqttDevice ";
            var table = App.OperateSql.GetTable(sql);
            foreach (DataRow row in table.Rows)
            {
                var device = new DeviceEntity();
                device.Id = Convert.ToInt32(row["Id"].ToString());
                device.Name = row["Name"].ToString();
                device.IsOnline = Convert.ToByte(row["isOnline"].ToString());
                result.Add(device);
            }

            return result;
        }

        /// <summary>
        /// 获取设备下所有数据点的最新数据
        /// </summary>
        /// <param name="deviceId">设备id</param>
        /// <param name="isCustom">是否自定义</param>
        /// <returns></returns>
        public static List<DataNodeEntity> GetDataNodes(int deviceId, bool isCustom = false)
        {
            List<DataNodeEntity> result = new List<DataNodeEntity>();
            var tags = GetTags(deviceId, isCustom);
            if (tags.Count != 0)
            {
                foreach (var tag in tags)
                {
                    DataNodeEntity dataNodeEntity = GetDataNode(deviceId, tag);
                    if (dataNodeEntity != null)
                    {
                        result.Add(dataNodeEntity);
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// 获取设备下所有tag
        /// </summary>
        /// <param name="deviceId"></param>
        /// <param name="isCustom">是否自定义</param>
        /// <returns></returns>
        public static List<string> GetTags(int deviceId, bool isCustom = false)
        {
            List<string> tags = new List<string>();
            string sql;
            if (isCustom)
            {
                sql = $"SELECT Tag " +
                      $"FROM DeviceDataTag " +
                      $"WHERE DeviceId = {deviceId} AND DataTypeId IN (1, 2, 3, 4)";
            }
            else
            {
                sql = $"SELECT Tag " +
                      $"FROM DeviceDataTag " +
                      $"WHERE DeviceId = {deviceId}";
            }

            var dataTable = App.OperateSql.GetTable(sql);
            foreach (DataRow dataTableRow in dataTable.Rows)
            {
                tags.Add(dataTableRow["Tag"].ToString());
            }

            return tags;
        }


        /// <summary>
        /// 获取指定数据
        /// </summary>
        /// <param name="deviceId"></param>
        /// <param name="tag"></param>
        /// <returns></returns>
        public static DataNodeEntity GetDataNode(int deviceId, string tag)
        {
            DataNodeEntity dataNode = null;
            // 根据id和tag判断是否存在
            string sql = $"SELECT COUNT(1) " +
                         $"FROM DeviceDataTag " +
                         $"WHERE DeviceId = {deviceId} And Tag = '{tag}'";
            int count = Convert.ToInt32(App.OperateSql.ExecuteScalar(sql).ToString());
            if (count != 1)
            {
                return null;
            }

            // 获取数据
            string sql2 = $"SELECT DataHistory.Value, DataHistory.Time, DataType.Unit, DeviceDataTag.Tag " +
                          $"FROM DataType " +
                          $"INNER JOIN DeviceDataTag " +
                          $"ON DataType.Id = DeviceDataTag.DataTypeId " +
                          $"INNER JOIN DataHistory " +
                          $"ON DataHistory.DeviceDataTagId = DeviceDataTag.Id " +
                          $"WHERE DeviceDataTag.Tag = '{tag}' " +
                          $"ORDER BY DataHistory.Time DESC LIMIT 1";
            var dataTable = App.OperateSql.GetTable(sql2);
            if (dataTable.Rows.Count < 1)
            {
                return null;
            }

            DataRow row = dataTable.Rows[0];
            dataNode = GetDataNode(row);
            return dataNode;
        }

        /// <summary>
        /// 获取设备数据
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public static DataNodeEntity GetDataNode(DataRow row)
        {
            DataNodeEntity dataNode = new DataNodeEntity();
            dataNode.Tag = row["Tag"].ToString();
            dataNode.Unit = row["Unit"].ToString();
            dataNode.Time = row["Time"].ToString();
            dataNode.Value = Convert.ToDouble(row["Value"].ToString());
            return dataNode;
        }

        /// <summary>
        /// 插入报警信息
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public static int InsertWarningInfo(WarningInfoEntity info)
        {
            string sql = $"INSERT INTO WarningInfo(Warning,Time) " +
                         $"VALUES('{info.Warning}','{info.Time}')";
            int index = App.OperateSql.ExecuteNoQuery(sql);
            return index;
        }

        /// <summary>
        /// 获取报警信息
        /// </summary>
        /// <param name="endTime">结束时间</param>
        /// <param name="startTime">开始时间</param>
        /// <returns></returns>
        public static List<WarningInfoEntity> GetWarningInfos()
        {
            List<WarningInfoEntity> result = new List<WarningInfoEntity>();

            string sql;

            sql = $"SELECT * FROM WarningInfo";

            var dataTable = App.OperateSql.GetTable(sql);
            foreach (DataRow row in dataTable.Rows)
            {
                WarningInfoEntity warningInfo = new WarningInfoEntity();
                warningInfo.Id = Convert.ToInt32(row["Id"]);
                warningInfo.Warning = row["Warning"].ToString();
                warningInfo.Time = row["Time"].ToString();
                result.Add(warningInfo);
            }

            return result;
        }

        public static WarningInfoEntity GetNewWarningInfo()
        {
            string sql = $"SELECT * FROM WarningInfo WHERE Id=(SELECT MAX(Id) FROM WarningInfo ) ";

            var dataTable = App.OperateSql.GetTable(sql);
            DataRow row = dataTable.Rows[0];
            WarningInfoEntity warningInfo = new WarningInfoEntity();
            warningInfo.Id = Convert.ToInt32(row["Id"]);
            warningInfo.Warning = row["Warning"].ToString();
            warningInfo.Time = row["Time"].ToString();
            return warningInfo;
        }
    }
}